package com.lm.activiti.boot.tools.excel.common;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;


/**
 * @author lm_jcf
 * @date 2018/6/25 10:43
 */
@Component
public class ExcelHandler<T> {

    public HSSFWorkbook buildExcel(HSSFWorkbook wb, ExportDto exportDto) throws Exception {
        String sheetName = exportDto.getSheetName();
        String searchMsg = exportDto.getSearchMsg();
        String title = exportDto.getTitle();
        Object[] headers = exportDto.getHeaders();
        Collection<T> dataset = exportDto.getDataset();
        String dataFormat = exportDto.getDateFormat();
        // 创建Excel工作表
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 设置表格默认列宽为17个字符
        sheet.setDefaultColumnWidth(13);

        // 创建标题样式
        HSSFCellStyle titleStyle = cellStyle(wb);
        titleStyle.setAlignment(HorizontalAlignment.LEFT); //水平布局：居中
        titleStyle.getFont(wb).setFontHeightInPoints((short) 12);
        titleStyle.getFont(wb).setBold(true);
        // 创建列名样式对象
        HSSFCellStyle headStyle = cellStyle(wb);
        headStyle.getFont(wb).setBold(true);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置背景色 HSSFColor.GREY_25_PERCENT
    /*    headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFPalette customPalette = wb.getCustomPalette();
        customPalette.setColorAtIndex(HSSFColor.ORANGE.index, (byte)242,(byte)242,(byte)242);
        headStyle.setFillForegroundColor(HSSFColor.ORANGE.index);*/

        // 创建内容样式
        HSSFCellStyle contentStyle = cellStyle(wb);
        contentStyle.setWrapText(true);

        // 居中内容样式
        HSSFCellStyle centerContentStyle = cellStyle(wb);
        centerContentStyle.setAlignment(HorizontalAlignment.CENTER);

        // 创建数值内容样式
        HSSFCellStyle integerContentStyle = cellStyle(wb);
        HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
        integerContentStyle.setDataFormat(df.getBuiltinFormat("#,#0")); // 数据格式只显示整数
        integerContentStyle.setAlignment(HorizontalAlignment.RIGHT);    // 右对齐

        // 创建病假内容样式
        HSSFCellStyle sickContentStyle = cellStyle(wb);
        sickContentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 修改poi里对应orange 颜色数值，实现改变颜色
        HSSFPalette customPalette = wb.getCustomPalette();
        customPalette.setColorAtIndex(HSSFColor.ORANGE.index, (byte)255,(byte)180,(byte)0);
        sickContentStyle.setFillForegroundColor(HSSFColor.ORANGE.index);

        // 创建事假内容样式
        HSSFCellStyle affairContentStyle = cellStyle(wb);
        affairContentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFPalette affairCustomPalette = wb.getCustomPalette();
        affairCustomPalette.setColorAtIndex(HSSFColor.RED.index, (byte)255,(byte)180,(byte)0);
        affairContentStyle.setFillForegroundColor(HSSFColor.RED.index);


        int startRow = 0;
        // 创建表格标题行
        if (StringUtils.isNotBlank(title)) {
            HSSFRow titleRow = sheet.createRow(startRow);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length));
            // 设置行高
            titleRow.setHeightInPoints((short)30);
            HSSFCell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(title);
            titleCell.setCellStyle(titleStyle);
            startRow ++;
        }

        //设置显示条件行
        if (StringUtils.isNotBlank(searchMsg)) {
            HSSFRow titleRow = sheet.createRow(startRow);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length));
            // 设置行高
            titleRow.setHeightInPoints((short)17);
            HSSFCell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(searchMsg);
            titleCell.setCellStyle(contentStyle);
            startRow ++;
        }

        // 创建列名行、设置列名
        HSSFRow headRow = sheet.createRow(startRow);
        headRow.setHeightInPoints(25);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = headRow.createCell(i);
            cell.setCellStyle(headStyle);
            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(headers[i]));
            cell.setCellValue(text);
        }

        // 遍历内容数据
        Iterator<T> it = dataset.iterator();

        HSSFRow row = null;
        while (it.hasNext()) {
            startRow ++;
            row = sheet.createRow(startRow);
            row.setHeightInPoints(17);

            T t = it.next();
            //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            int extraCell = 0;
            for (int i = 0; i < fields.length; i++) {

                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                Class tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                Object value = getMethod.invoke(t, new Object[]{});

                if (value == null) {
                    continue;
                }

                if (value instanceof Map) {
                    for (Object o : ((Map) value).keySet()) {
                        HSSFCell cell = row.createCell((Integer) o);

                        String values = ((Map) value).get(o).toString();
                        cell.setCellValue(values);
                        if (StringUtils.equals("病假", values)) {
                            cell.setCellStyle(sickContentStyle);
                        } else if (StringUtils.equals("事假", values)) {
                            cell.setCellStyle(affairContentStyle);
                        } else {
                            cell.setCellStyle(contentStyle);
                        }
                    }

                } else if (value instanceof Integer) {
                    HSSFCell cell = row.createCell(i);

                    cell.setCellStyle(integerContentStyle);
                    cell.setCellValue(((Integer) value).intValue());

                } else {
                    String textValue = value.toString();

                    if (value instanceof Date) {
                        Date date = (Date) value;
//                        textValue = CoreUtils.convertDateToString(date, dataFormat);
                    }

                    if (StringUtils.isNotBlank(textValue)) {
                        HSSFCell cell = row.createCell(i);
                        cell.setCellValue(textValue);

                        // 班级居中显示
                        if (StringUtils.equalsIgnoreCase(fieldName, "className")) {
                            cell.setCellStyle(centerContentStyle);
                        } else {
                            cell.setCellStyle(contentStyle);
                        }
                    }
                }
            }
        }

        return wb;
    }


    public static HSSFCellStyle cellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();

        // 设置左对齐内容
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont cell_Font = wb.createFont();
        cell_Font.setFontHeightInPoints((short) 11);
        cell_Font.setFontName("宋体");
        cellStyle.setFont(cell_Font);
        return cellStyle;
    }
}